﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MixScript.BLL;

namespace MixScript.BLL.FI
{
    public class BSubsidiary : cBaseBLL
    {

        public BSubsidiary()
        {

        }

        public bool getSubsidiary(ref DataTable dt, Int32 process)
        {

            try
            {
                dt = new DataTable();
                string sSQL = @"SELECT a.acctnum,a.acctname,a.dbcd,a.currency,SUM(l.debet) as debet, SUM(l.kredit) as kredit 
                                FROM accaccounts a LEFT JOIN 
                                (SELECT acctnum, Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                                CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit 
                                FROM accledger) AS l ON l.acctnum = a.acctnum  
                                WHERE process = '{0}'
                                GROUP BY a.acctnum ,a.acctname,a.dbcd,a.currency";
                dt = Dbserver.ExecuteQuery(string.Format(sSQL, process), "Bsubsidiary");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public bool getSubSubsidiary(ref DataTable dt, Int32 process, string accnum)
        {
            try
            {
                dt = new DataTable();
                string sSQL;
                sSQL = @"SELECT b.banknum code,b.bankname name,s.process,s.dbcd,s.currency, 
                    ISNULL(s.debet,0) as debet, isnull(s.kredit,0) as kredit
                    FROM mst_bank b LEFT JOIN (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}' 
                    ) s ON b.banknum=s.subledger ORDER BY b.banknum";

                if (process == 3)
                    sSQL = @"SELECT b.banknum code,b.bankname name,s.process,s.dbcd,s.currency, 
                    ISNULL(s.debet,0) as debet, isnull(s.kredit,0) as kredit
                    FROM mst_bank b LEFT JOIN (SELECT sl.subledger,a.process,a.dbcd,a.currency, 
                    Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger sl
                    LEFT JOIN accaccounts a ON sl.acctnum=a.acctnum WHERE sl.acctnum = '{0}'
                    ) s ON b.banknum=s.subledger ORDER BY b.banknum";
                else if (process == 4)
                    sSQL = @"SELECT c.cust_code code,c.cust_name name,'' currency,'' dbcd, ISNULL(s.debet,0) as debet, ISNULL(s.kredit,0) as kredit 
                    FROM mst_customer c LEFT JOIN (SELECT subledger,Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit  FROM accsubledger 
                    WHERE acctnum = '{0}') s ON c.cust_code=s.subledger ORDER BY c.cust_code";
                else if (process == 6)
                    sSQL = @"SELECT e.empl_id code,e.nama name,'' currency,'' dbcd, ISNULL(s.debet,0) as debet, ISNULL(s.kredit,0) as kredit 
                    FROM hrmkaryawan e LEFT JOIN (SELECT subledger,Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
                    CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit   FROM accsubledger 
                    WHERE acctnum ='{0}') s ON e.empl_id=s.subledger ORDER BY e.empl_id";
                else if (process == 7)
                    sSQL = @"SELECT s.sup_code code,s.sup_name name,'' currency,'' dbcd, ISNULL(m.debet,0) as debet, ISNULL(m.kredit,0) as kredit 
                      FROM mst_supplier s LEFT JOIN (SELECT subledger,
                      Case saldodbcd when 'D'THEN valconvert ELSE 0 end debet, 
	                  CASE saldodbcd WHEN 'C' THen valconvert ELSE 0 END kredit FROM accsubledger 
                      WHERE acctnum = '{0}') m ON s.sup_code=m.subledger
                      ORDER BY s.sup_code";

                dt = Dbserver.ExecuteQuery(string.Format(sSQL, accnum), "SUB");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        public bool SaveSubSidiary(Int32 acctnum, Int32 process, string Name, string dbc, Int64 value, int id_period)
        {
            try
            {
                string sSQL;
                string sSQL1;

                sSQL = @"DELETE accsubledger where acctnum = '{0}'";
                Dbserver.ExecuteNonQuery(string.Format(sSQL, acctnum));

                sSQL1 = @"INSERT INTO accsubledger (kd_cmpy, acctnum, process, subledger, saldodbcd, valconvert,period_id ) 
                    VALUES (1,{0},{1},{2}, {3}, {4},{5})";

                return Dbserver.ExecuteNonQuery(string.Format(sSQL1, acctnum, process, Name, dbc, value, id_period));

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}
